/*
 * Created on Oct 9, 2003
 * CategoryDB
 * Local Projects Database
 * (c) 2003 eRomania Gateway Association
 *
 */
package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.Category;
import ro.gateway.aida.struts.form.EditCategoryForm;
import ro.gateway.aida.utils.LocalisedSortableList;


/**
 * @author Mihai Postelnicu
 * CategoryDB
 *
 *  *
 */
public class CategoryDB extends DBPersistenceManager {
	private CategoryDB( PersistenceToken token )
			{
				  super( token );
			}

			public static CategoryDB getManager( PersistenceToken token )
			{
				  return new CategoryDB( token );
			}

    public int getMax() throws SQLException {
        Connection con = getConnection();

        try {
            PreparedStatement ps = con.prepareStatement(GET_LAST);
            ResultSet rs = ps.executeQuery();

            if (rs.next())
                return rs.getInt(1);

            rs.close();
            ps.close();
           
        }finally {
            con.close();
        }

        return 0;
    }

    public int addLang(EditCategoryForm c, String lang) throws SQLException{
        Connection con = getConnection();

        if (c.getId() == 0)
            c.setId(getMax() + 1);

        try {
            PreparedStatement ps = con.prepareStatement(ADD_CATEGORY_LANG);
            ps.setInt(1, c.getId());
            ps.setString(2, lang);

            ps.setString(3, c.getName());
            ps.executeUpdate();
            ps.close();
        } finally {
            con.close();
        }

        return c.getId();
    }

    public void updateLang(EditCategoryForm c, String lang) throws SQLException {
        if (!isCatPresent(c.getId(), lang)) {
            addLang(c, lang);

            return;
        }

        Connection con = getConnection();

        try {
            PreparedStatement ps = con.prepareStatement(UPDATE_CATEGORY_LANG);
            ps.setInt(2, c.getId());
            ps.setString(3, lang);
            ps.setString(1, c.getName());
            ps.executeUpdate();
            ps.close();
        } finally{
            con.close();
        }
    }

    


	public LocalisedSortableList getAllLocalized(String lang)throws SQLException {
		LocalisedSortableList v = new LocalisedSortableList(lang,new Category.NameComparator());
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(GET_ALL_IDS);

			ResultSet rs = ps.executeQuery();

			while (rs.next())
				v.add(get(rs.getInt(1)));

			rs.close();
			ps.close();
			
		}finally {
			con.close();
					}

		return v;
	}

    public ArrayList getAll(String lang) throws SQLException{
        ArrayList v = new ArrayList();
        Connection con = getConnection();

        try {
            PreparedStatement ps = con.prepareStatement(GET_ALL_IDS);

            ResultSet rs = ps.executeQuery();

            while (rs.next())
                v.add(get(rs.getInt(1), lang));

            rs.close();
            ps.close();
        } finally {
            con.close();
        }

        return v;
    }

    public EditCategoryForm getFirstLanguage(int id) throws SQLException{
        Connection con = getConnection();
        EditCategoryForm c = new EditCategoryForm();
        c.setId(id);

        try {
            PreparedStatement ps = con.prepareStatement(GET_CATEGORY_FIRST_LANG);
            ps.setInt(1, id);

            ResultSet rs = ps.executeQuery();

            if (rs.next())
                c.setName(rs.getString(1) + " (" + rs.getString(2) + ")");

            rs.close();
            ps.close();
        } finally {
            	con.close();
        }

        return c;
    }

    public EditCategoryForm get(int id, String lang)throws SQLException {
        Connection con = getConnection();
        EditCategoryForm c = new EditCategoryForm();
        c.setId(id);

        try {
            PreparedStatement ps = con.prepareStatement(GET_CATEGORY);
            ps.setInt(1, id);
            ps.setString(2, lang);

            ResultSet rs = ps.executeQuery();

            if (rs.next())
                c.setName(rs.getString(1));
            else {
                rs.close();
                ps.close();
               //con.close();
                return getFirstLanguage(id);
            }

            rs.close();
            ps.close();
        } finally {
          con.close();
        }

        return c;
    }

    public boolean isCatPresent(int id, String lang) throws SQLException{
        Connection con = getConnection();
        int k = 0;

        try {
            PreparedStatement ps = con.prepareStatement(ISLANG);
            ps.setInt(1, id);
            ps.setString(2, lang);

            ResultSet rs = ps.executeQuery();

            if (rs.next())
                k = rs.getInt(1);

            rs.close();
            ps.close();
          
        } finally {
           con.close();
        }

        return (k == 0) ? false : true;
    }

    public boolean delete(int id) throws SQLException {
        Connection con = getConnection();

        try {
            PreparedStatement ps = con.prepareStatement(DELETE_CATEGORY);
            ps.setInt(1, id);
            ps.executeUpdate();
            ps.close();
            return true;
        } catch (SQLException e) {
        	//just verify if has dependancies
            return false;
        } finally {
        	con.close();
        }
  	  
    }

	public Category get(int id) throws SQLException {
		Connection con=getConnection();
		Category c=null;
		try {
			PreparedStatement ps=con.prepareStatement(GET_FULL);
			ps.setInt(1,id);
			ResultSet rs=ps.executeQuery();
			c=new Category();
			while(rs.next()) {
				c.setId(rs.getInt(1));
				c.getName().setString(rs.getString(2),rs.getString(3));
			}
			
			rs.close();
			ps.close();
			return c;
		} finally {
			con.close();
		}
	}

	private static final String GET_FULL="SELECT id,lang,name FROM categories WHERE id=?";
    private static final String ISLANG = "SELECT count(*) FROM categories WHERE id=? AND lang=?";
    private static final String GET_ALL_IDS = "SELECT DISTINCT id FROM categories";
    private static final String DELETE_CATEGORY = "DELETE FROM categories WHERE id=?";
    private static final String DELETE_CATEGORY_LANG = "DELETE FROM categories WHERE id=?";
    private static final String UPDATE_CATEGORY_LANG =
        "UPDATE categories SET name=? " + "WHERE id=? AND lang=?";
    private static final String GET_LAST = "SELECT MAX(id) FROM categories";
    private static final String GET_ALL = "SELECT id,lang,name FROM categories WHERE lang=? ORDER BY id,lang";
    private static final String ADD_CATEGORY_LANG =
        "INSERT INTO categories (id,lang,name) " + "VALUES (?,?,?)";
    private static final String GET_CATEGORY = "SELECT name FROM categories " +
        "WHERE id=? AND lang=?";
    private static final String GET_CATEGORY_FIRST_LANG = "SELECT name,lang FROM categories " +
		"WHERE id=?";

}
